package com.homework.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.struts2.ServletActionContext;

import com.homework.bean.CourseBean;
import com.homework.bean.MD5Bean;
import com.homework.bean.PageInfoBean;
import com.homework.bean.UserBean;
import com.homework.bean.UserListBean;
import com.homework.service.PageInfoService;
import com.homework.util.Constants;
import com.homework.util.connDB;

public class UserDao {

	private static UserDao userDao = null;
	private connDB conn = new connDB();
	private ResultSet rs;
	private String sql = "";

	private PageInfoService pageInfoService;

	public UserDao() {
		pageInfoService = new PageInfoService();
	}

	public static UserDao getInstance() {
		if (userDao == null) {
			userDao = new UserDao();
		}
		return userDao;
	}

	/**
	 * 登陆
	 * 
	 * @param userBean
	 * @return
	 * @throws SQLException
	 */
	public int login(UserBean userBean) throws SQLException {
		int result = 0;
		String username = userBean.getUsername();
		String password = MD5Bean.createPassword(userBean.getPassword());
		try {
			sql = "select f_userId,f_roleId,f_userStatus from t_user where f_username='"
					+ username + "' and  f_password = '" + password + "'";
			rs = conn.executeQuery(sql);
			if (rs.next()) {
				int roleId = Integer.parseInt(rs.getString("f_roleId"));
				int userStatus = Integer.parseInt(rs.getString("f_userStatus"));
				switch (userStatus) {
				// 该用户还未通过审核
				case 1:
					result = Constants.RESULT_UserVerifyMissing;
					break;
				// 通过验证，赋予角色
				case 2:
					result = roleId;
					break;
				// 该用户已在线（暂时允许在线登陆，之后通过监听器实现禁止重复登陆）
				case 4:
					result = roleId;
					break;
				// 该用户已删除
				case 5:
					result = Constants.RESULT_UserDeleted;
					break;
				// 系统异常
				default:
					result = Constants.RESULT_SystemAbnormal;
				}
				userBean.setUserId(rs.getString("f_userId"));
				rs.close();
				conn.close();
				return result;
			}
			// 用户名或密码输入错误
			result = Constants.RESULT_UsernameAndPwdWrong;
		} catch (Exception e) {
			e.printStackTrace();
		}
		rs.close();
		conn.close();
		return result;
	}

	/**
	 * 注册业务
	 * 
	 * @param UserBean
	 * @return true 注册成功 false 注册失败
	 */
	public int regist(UserBean userBean) {
		int userId = 0;
		String realname = userBean.getRealname();
		String username = userBean.getUsername();
		String password = MD5Bean.createPassword(userBean.getPassword());
		String phone = userBean.getPhone();
		String email = userBean.getEmail();
		String roleId = userBean.getRoleId();
		String institute = userBean.getInstitute();
		String grade = userBean.getGrade();
		String major = userBean.getMajor();
		String classes = userBean.getClasses();
		String university = userBean.getUniversity();

		SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		String createTime = sf.format(new Date().getTime());

		try {
			sql = "insert into t_user values (null,'" + username + "','"
					+ realname + "','" + password + "',1,'" + phone + "','"
					+ email + "',null,null,'" + createTime + "',0," + roleId
					+ ",'" + university + "','" + institute + "','" + grade
					+ "','" + major + "','" + classes + "');";
			int result = conn.executeUpdate(sql);
			if (result == 1) {
				userId = findUserIdByUsername(username);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return userId;
	}

	// 通过用户名找到用户id
	public int findUserIdByUsername(String username) {
		int userId = 0;
		String sql = "select f_userId from t_user where f_username='"
				+ username + "'";
		rs = conn.executeQuery(sql);
		try {
			if (rs.next()) {
				userId = Integer.parseInt(rs.getString(1));
				rs.close();
			}
		} catch (NumberFormatException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return userId;
	}

	public int findRoleIdByUsername(String username) {
		int roleId = 0;
		sql = "select f_roleId from t_user where f_username='" + username + "'";
		rs = conn.executeQuery(sql);

		try {
			if (rs.next()) {
				roleId = Integer.parseInt(rs.getString("f_roleId"));
				rs.close();
			}
		} catch (NumberFormatException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}

		return roleId;

	}

	/**
	 * 验证用户名唯一性
	 * 
	 * @param String
	 *            username
	 * 
	 * @return true 用户名已存在 false 用户名不存在
	 */
	public boolean usernameCheck(String username) {
		boolean flag = false;

		sql = "select f_username from t_user where f_username = '" + username
				+ "'";
		rs = conn.executeQuery(sql);
		try {
			if (rs.next()) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return flag;
	}

	/**
	 * 修改密码
	 * 
	 * @param userId
	 *            newPassWord
	 * @return true 修改成功 false 修改失败
	 */
	public boolean changePassword(Object userId, String newPassword) {
		String password = MD5Bean.createPassword(newPassword);
		sql = "update t_user set f_password='" + password
				+ "' where f_userId = '" + userId + "'";
		int result = conn.executeUpdate(sql);
		conn.close();
		if (result == 1) {
			return true;
		}

		return false;
	}

	/**
	 * 验证旧密码
	 * 
	 * @param int userId
	 * @param String
	 *            oldPassword
	 * @return true 密码正确 false 密码错误
	 */
	public boolean passwordCheck(Object userId, String oldPassword) {

		String password = MD5Bean.createPassword(oldPassword);
		boolean flag = false;
		sql = "select * from t_user where f_userId = '" + userId
				+ "' and f_password = '" + password + "'";
		rs = conn.executeQuery(sql);
		try {
			if (rs.next()) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return flag;
	}

	// 登陆成功后更新 f_lasttime
	public boolean updateLoginTime(int userId) {
		// 获取当前系统时间
		SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		String lastLoginTime = sf.format(new Date().getTime());

		sql = "update t_user set f_lasttime ='" + lastLoginTime
				+ "' where f_userId = '" + userId + "'";
		int result = conn.executeUpdate(sql);
		conn.close();
		if (result == 1) {
			return true;
		}
		return false;
	}

	// 登陆成功后更新用户ip
	public boolean updateLoginIp(int userId) {
		// 获取用户ip
		String loginIp = ServletActionContext.getRequest().getRemoteAddr();
		sql = "update t_user set f_loginip ='" + loginIp
				+ "' where f_userId = '" + userId + "'";
		int result = conn.executeUpdate(sql);
		conn.close();
		if (result == 1) {
			return true;
		}
		return false;
	}

	// 登陆成功后更新用户状态
	public boolean updateUserStatus(Object userId, int userStatus) {
		sql = "update t_user set f_userStatus = '" + userStatus
				+ "' where f_userId = '" + userId + "'";
		int result = conn.executeUpdate(sql);
		conn.close();
		if (result == 1) {
			return true;
		}
		return false;
	}

	// 学生列表
	public UserListBean studentShow(CourseBean courseBean,
			PageInfoBean pageInfoBean) throws SQLException {
		// 数据库连接
		sql = "select t_userTemp.* from (select * from t_user where f_roleId = 1 and (f_userStatus = 2 or f_userStatus = 4) ) as t_userTemp natural join (select f_userId from t_choose where f_chooseStatus = 1 and f_courseId = "
				+ courseBean.getCourseId() + ") as t_chooseTemp;";
		rs = conn.executeQuery(sql);

		// --------------分页基本参数获取---------------
		pageInfoBean.setRs(rs);
		pageInfoService.setPageInfoBean(pageInfoBean);

		// 获取StudentHomeworkListBean
		UserListBean userListBean = new UserListBean();
		for (int i = 0; i < pageInfoBean.getNum(); i++) {
			UserBean userBean = new UserBean();
			userBean.setUserId(rs.getString("f_userId"));
			userBean.setUsername(rs.getString("f_username"));
			userBean.setRealname(rs.getString("f_realname"));
			userBean.setUserStatus(rs.getString("f_userStatus"));
			userBean.setPhone(rs.getString("f_phone"));
			userBean.setEmail(rs.getString("f_email"));
			userBean.setLasttime(rs.getString("f_lasttime"));
			userBean.setLoginip(rs.getString("f_loginip"));
			userBean.setCreatonTime(rs.getString("f_creationTime"));
			userBean.setRoleId(rs.getString("f_roleId"));
			userBean.setUniversity(rs.getString("f_university"));
			userBean.setInstitute(rs.getString("f_institute"));
			userBean.setGrade(rs.getString("f_grade"));
			userBean.setMajor(rs.getString("f_major"));
			userBean.setClasses(rs.getString("f_classes"));
			userListBean.getUserList().add(userBean);
			rs.next(); // 下一条记录
		}

		rs.close();
		conn.close();
		return userListBean;
	}

	// 学生查询
	public UserListBean studentQuery(CourseBean courseBean,
			PageInfoBean pageInfoBean, UserBean userBean) throws SQLException {
		// 数据库连接
		sql = "select t_userTemp.* from (select * from t_user where f_roleId = 1 and (f_userStatus = 2 or f_userStatus = 4) and f_realname like '%"
				+ userBean.getRealname()
				+ "%') as t_userTemp natural join (select f_userId from t_choose where f_chooseStatus = 1 and f_courseId = "
				+ courseBean.getCourseId() + ") as t_chooseTemp;";
		rs = conn.executeQuery(sql);

		// --------------分页基本参数获取---------------
		pageInfoBean.setRs(rs);
		pageInfoService.setPageInfoBean(pageInfoBean);

		// 获取StudentHomeworkListBean
		UserListBean userListBean = new UserListBean();
		for (int i = 0; i < pageInfoBean.getNum(); i++) {
			UserBean userBeanTemp = new UserBean();
			userBeanTemp.setUserId(rs.getString("f_userId"));
			userBeanTemp.setUsername(rs.getString("f_username"));
			userBeanTemp.setRealname(rs.getString("f_realname"));
			userBeanTemp.setUserStatus(rs.getString("f_userStatus"));
			userBeanTemp.setPhone(rs.getString("f_phone"));
			userBeanTemp.setEmail(rs.getString("f_email"));
			userBeanTemp.setLasttime(rs.getString("f_lasttime"));
			userBeanTemp.setLoginip(rs.getString("f_loginip"));
			userBeanTemp.setCreatonTime(rs.getString("f_creationTime"));
			userBeanTemp.setRoleId(rs.getString("f_roleId"));
			userBeanTemp.setUniversity(rs.getString("f_university"));
			userBeanTemp.setInstitute(rs.getString("f_institute"));
			userBeanTemp.setGrade(rs.getString("f_grade"));
			userBeanTemp.setMajor(rs.getString("f_major"));
			userBeanTemp.setClasses(rs.getString("f_classes"));
			userListBean.getUserList().add(userBeanTemp);
			rs.next(); // 下一条记录
		}

		rs.close();
		conn.close();
		return userListBean;
	}

	// 通过userId查找用户信息并返回UserBean
	public UserBean getUserInfo(String userId) {
		UserBean userBean = new UserBean();
		sql = "select * from t_user where f_userId = " + userId + "";
		rs = conn.executeQuery(sql);
		try {
			if (rs.next()) {
				userBean.setUserId(rs.getString("f_userId"));
				userBean.setUsername(rs.getString("f_username"));
				userBean.setRealname(rs.getString("f_realname"));
				userBean.setPhone(rs.getString("f_phone"));
				userBean.setEmail(rs.getString("f_email"));
				userBean.setRoleId(rs.getString("f_roleId"));
				userBean.setUniversity(rs.getString("f_university"));
				userBean.setInstitute(rs.getString("f_institute"));
				userBean.setGrade(rs.getString("f_grade"));
				userBean.setMajor(rs.getString("f_major"));
				userBean.setClasses(rs.getString("f_classes"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conn.close();
		}
		return userBean;
	}

	// 保存用户信息
	public boolean setUserInfo(UserBean userBean) {
		sql = "update t_user set f_realname = '" + userBean.getRealname() + "',f_phone = '"
				+ userBean.getPhone() + "',f_email='" + userBean.getEmail()
				+ "',f_university = '" + userBean.getUniversity()
				+ "',f_institute = '" + userBean.getInstitute() + "',f_grade='"
				+ userBean.getGrade() + "',f_major = '" + userBean.getMajor()
				+ "',f_classes = '" + userBean.getClasses()
				+ "'  where f_userId = " + userBean.getUserId() + "";
		int result = conn.executeUpdate(sql);
		conn.close();
		if (result == 1) {
			return true;
		}
		return false;
	}

	public int getIdbyName(String name) {
		// TODO Auto-generated method stub
		int ret = 0;
		sql = "select f_userId from t_user where f_username = '" + name + "'";
		rs = conn.executeQuery(sql);
		try {
			if (rs.next()) {
				ret = rs.getInt("f_userId");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conn.close();
		}
		return ret;
	}

	// 用户查询（管理员）
	public UserListBean userQuery(PageInfoBean pageInfoBean, UserBean userBean)
			throws SQLException {
		// 数据库连接
		sql = "select * from t_user where f_realname like '%"
				+ userBean.getRealname() + "%';";
		rs = conn.executeQuery(sql);

		// --------------分页基本参数获取---------------
		pageInfoBean.setRs(rs);
		pageInfoService.setPageInfoBean(pageInfoBean);

		// 获取StudentHomeworkListBean
		UserListBean userListBean = new UserListBean();
		for (int i = 0; i < pageInfoBean.getNum(); i++) {
			UserBean userBeanTemp = new UserBean();
			userBeanTemp.setUserId(rs.getString("f_userId"));
			userBeanTemp.setUsername(rs.getString("f_username"));
			userBeanTemp.setRealname(rs.getString("f_realname"));
			userBeanTemp.setUserStatus(rs.getString("f_userStatus"));
			userBeanTemp.setPhone(rs.getString("f_phone"));
			userBeanTemp.setEmail(rs.getString("f_email"));
			userBeanTemp.setLasttime(rs.getString("f_lasttime"));
			userBeanTemp.setLoginip(rs.getString("f_loginip"));
			userBeanTemp.setCreatonTime(rs.getString("f_creationTime"));
			userBeanTemp.setRoleId(rs.getString("f_roleId"));
			userBeanTemp.setUniversity(rs.getString("f_university"));
			userBeanTemp.setInstitute(rs.getString("f_institute"));
			userBeanTemp.setGrade(rs.getString("f_grade"));
			userBeanTemp.setMajor(rs.getString("f_major"));
			userBeanTemp.setClasses(rs.getString("f_classes"));
			userListBean.getUserList().add(userBeanTemp);
			rs.next(); // 下一条记录
		}

		rs.close();
		conn.close();
		return userListBean;
	}

	// 用户列表（管理员）
	public UserListBean userAuditing(PageInfoBean pageInfoBean)
			throws SQLException {
		// 数据库连接
		sql = "select * from t_user;";
		rs = conn.executeQuery(sql);

		// --------------分页基本参数获取---------------
		pageInfoBean.setRs(rs);
		pageInfoService.setPageInfoBean(pageInfoBean);

		// 获取StudentHomeworkListBean
		UserListBean userListBean = new UserListBean();
		for (int i = 0; i < pageInfoBean.getNum(); i++) {
			UserBean userBeanTemp = new UserBean();
			userBeanTemp.setUserId(rs.getString("f_userId"));
			userBeanTemp.setUsername(rs.getString("f_username"));
			userBeanTemp.setRealname(rs.getString("f_realname"));
			userBeanTemp.setUserStatus(rs.getString("f_userStatus"));
			userBeanTemp.setPhone(rs.getString("f_phone"));
			userBeanTemp.setEmail(rs.getString("f_email"));
			userBeanTemp.setLasttime(rs.getString("f_lasttime"));
			userBeanTemp.setLoginip(rs.getString("f_loginip"));
			userBeanTemp.setCreatonTime(rs.getString("f_creationTime"));
			userBeanTemp.setRoleId(rs.getString("f_roleId"));
			userBeanTemp.setUniversity(rs.getString("f_university"));
			userBeanTemp.setInstitute(rs.getString("f_institute"));
			userBeanTemp.setGrade(rs.getString("f_grade"));
			userBeanTemp.setMajor(rs.getString("f_major"));
			userBeanTemp.setClasses(rs.getString("f_classes"));
			userListBean.getUserList().add(userBeanTemp);
			rs.next(); // 下一条记录
		}

		rs.close();
		conn.close();
		return userListBean;
	}

	// 用户启用或通过审核
	public void userPass(UserBean userBean) {
		sql = "update t_user set f_userStatus = 2 where f_userId = "
				+ userBean.getUserId() + ";";
		conn.executeUpdate(sql);
		conn.close();
	}

	// 禁用用户
	public void userForbid(UserBean userBean) {
		sql = "update t_user set f_userStatus = 5 where f_userId = "
				+ userBean.getUserId() + ";";
		conn.executeUpdate(sql);
		conn.close();
	}

}
